﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using SIPO.Library;
using SIPO.Library.DomainObject.Transaction;
using SIPO.Library.Enum;
using SIPO.Library.Interfaces;
using SIPO.Library.DomainObject.Administration.Catalog;

namespace SIPO.Library.DataAccess.Transaction
{
    public partial class recObjectionRepo 
    {
        public object SelectScalar(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max: 
                            sbQuery.AppendFormat("SELECT MAX({0}) FROM transactions.recObjection {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Min:
                            sbQuery.AppendFormat("SELECT MIN({0}) FkROM transactions.recObjection {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat("SELECT DISTINCT({0}) FROM transactions.recObjection {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat(@"
                            SELECT COUNT({0})  
                                from transactions.recObjection 
                            LEFT JOIN master.recDebtorSector on (transactions.recObjection.DebtorSectorID = master.recDebtorSector.DebtorSectorID)
                            LEFT JOIN master.recDebtor on (master.recDebtor.DebtorID = master.recDebtorSector.DebtorID)
                            {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("SELECT SUM({0}) FROM transactions.recObjection {1}", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat("SELECT AVG({0}) FROM transactions.recObjection {1}", column, parameters);
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        public List<recObjection> GetListDecision(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<recObjection> tt = new List<recObjection>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);
                    
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat("        master.recDebtor.DebtorName, ObjectionID, transactions.recObjection.ObjectionNo, ObjectionDate, SanctionID, SanctionNo, SanctionDate, SanctionValue, transactions.recObjection.DebtorSectorID, ObjectionValue, ObjectionNote, TransNo, TransStatus, ResponNo, ResponDate, ResponNote, Decision, DecisionNo, DecisionDate, DecisionNote, DecisionValue, transactions.recObjection.IsActive, FileNameObjection, FileNameRespon, FileNameDecisionFile, transactions.recObjection.CreatedDate, transactions.recObjection.CreatedBy, transactions.recObjection.UpdatedDate, transactions.recObjection.UpdatedBy  ");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat("      transactions.recObjection left join master.recDebtorSector on (transactions.recObjection.DebtorSectorID = master.recDebtorSector.DebtorSectorID) ");
                    sbQuery.AppendFormat("      left join master.recDebtor on (master.recDebtor.DebtorID = master.recDebtorSector.DebtorID) ");
                    //sbQuery.AppendFormat("      left join transactions.recBilling on (transactions.recBilling.ObjectionNo = transactions.recObjection.ObjectionNo) ");
                    //sbQuery.AppendFormat("      where transactions.recObjection.ObjectionNo = transactions.recBilling.ObjectionNo and transactions.recBilling.IsObjectionStatus is null and transactions.recBilling.IsHold is null ");
                    sbQuery.AppendFormat("    {0} ", parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recObjection t = new recObjection(true);
                                //t.recSanction.InvoiceNo = dr[recSanction.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recSanction.Fields.InvoiceNo].ToString();
                                t.ObjectionID = dr[recObjection.Fields.ObjectionID] == DBNull.Value ? 0 : int.Parse(dr[recObjection.Fields.ObjectionID].ToString());
                                t.recDebtor.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();
                                t.ObjectionNo = dr[recObjection.Fields.ObjectionNo] == DBNull.Value ? string.Empty : dr[recObjection.Fields.ObjectionNo].ToString();
                                t.ObjectionDate = dr[recObjection.Fields.ObjectionDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recObjection.Fields.ObjectionDate].ToString());
                                t.SanctionID = dr[recObjection.Fields.SanctionID] == DBNull.Value ? (int?)null : int.Parse(dr[recObjection.Fields.SanctionID].ToString());
                                t.SanctionNo = dr[recObjection.Fields.SanctionNo] == DBNull.Value ? string.Empty : dr[recObjection.Fields.SanctionNo].ToString();
                                t.SanctionDate = dr[recObjection.Fields.SanctionDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recObjection.Fields.SanctionDate].ToString());
                                t.SanctionValue = dr[recObjection.Fields.SanctionValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recObjection.Fields.SanctionValue].ToString());
                                t.DebtorSectorID = dr[recObjection.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recObjection.Fields.DebtorSectorID].ToString());
                                t.ObjectionValue = dr[recObjection.Fields.ObjectionValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recObjection.Fields.ObjectionValue].ToString());
                                t.ObjectionNote = dr[recObjection.Fields.ObjectionNote] == DBNull.Value ? string.Empty : dr[recObjection.Fields.ObjectionNote].ToString();
                                t.TransNo = dr[recObjection.Fields.TransNo] == DBNull.Value ? string.Empty : dr[recObjection.Fields.TransNo].ToString();
                                t.TransStatus = dr[recObjection.Fields.TransStatus] == DBNull.Value ? string.Empty : dr[recObjection.Fields.TransStatus].ToString();
                                t.ResponNo = dr[recObjection.Fields.ResponNo] == DBNull.Value ? string.Empty : dr[recObjection.Fields.ResponNo].ToString();
                                t.ResponDate = dr[recObjection.Fields.ResponDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recObjection.Fields.ResponDate].ToString());
                                t.ResponNote = dr[recObjection.Fields.ResponNote] == DBNull.Value ? string.Empty : dr[recObjection.Fields.ResponNote].ToString();
                                t.Decision = dr[recObjection.Fields.Decision] == DBNull.Value ? string.Empty : dr[recObjection.Fields.Decision].ToString();
                                t.DecisionNo = dr[recObjection.Fields.DecisionNo] == DBNull.Value ? string.Empty : dr[recObjection.Fields.DecisionNo].ToString();
                                t.DecisionDate = dr[recObjection.Fields.DecisionDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recObjection.Fields.DecisionDate].ToString());
                                t.DecisionNote = dr[recObjection.Fields.DecisionNote] == DBNull.Value ? string.Empty : dr[recObjection.Fields.DecisionNote].ToString();
                                t.DecisionValue = dr[recObjection.Fields.DecisionValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recObjection.Fields.DecisionValue].ToString());
                                t.IsActive = dr[recObjection.Fields.IsActive] == DBNull.Value ? (bool?)null : bool.Parse(dr[recObjection.Fields.IsActive].ToString());
                                t.FileNameObjection = dr[recObjection.Fields.FileNameObjection] == DBNull.Value ? string.Empty : dr[recObjection.Fields.FileNameObjection].ToString();
                                t.FileNameRespon = dr[recObjection.Fields.FileNameRespon] == DBNull.Value ? string.Empty : dr[recObjection.Fields.FileNameRespon].ToString();
                                t.FileNameDecisionFile = dr[recObjection.Fields.FileNameDecisionFile] == DBNull.Value ? string.Empty : dr[recObjection.Fields.FileNameDecisionFile].ToString();
                                t.CreatedDate = dr[recObjection.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recObjection.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recObjection.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recObjection.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recObjection.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recObjection.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recObjection.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recObjection.Fields.UpdatedBy].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public List<recObjection> GetListResponse(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<recObjection> tt = new List<recObjection>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat("      master.recDebtor.DebtorName, ObjectionID, ObjectionNo, ObjectionDate, SanctionID, SanctionNo, SanctionDate, SanctionValue, transactions.recObjection.DebtorSectorID, ObjectionValue, ObjectionNote, TransNo, TransStatus, ResponNo, ResponDate, ResponNote, Decision, DecisionNo, DecisionDate, DecisionNote, DecisionValue, transactions.recObjection.IsActive, FileNameObjection, FileNameRespon, FileNameDecisionFile, transactions.recObjection.CreatedDate, transactions.recObjection.CreatedBy, transactions.recObjection.UpdatedDate, transactions.recObjection.UpdatedBy  ");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat("      transactions.recObjection left join master.recDebtorSector on (transactions.recObjection.DebtorSectorID = master.recDebtorSector.DebtorSectorID) ");
                    sbQuery.AppendFormat("      left join master.recDebtor on (master.recDebtor.DebtorID = master.recDebtorSector.DebtorID) ");
                    sbQuery.AppendFormat("    where transactions.recObjection.DecisionNo IS Null  ");
                    sbQuery.AppendFormat("    {0} ", parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recObjection t = new recObjection(true);
                                t.ObjectionID = dr[recObjection.Fields.ObjectionID] == DBNull.Value ? 0 : int.Parse(dr[recObjection.Fields.ObjectionID].ToString());
                                t.recDebtor.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();
                                t.ObjectionNo = dr[recObjection.Fields.ObjectionNo] == DBNull.Value ? string.Empty : dr[recObjection.Fields.ObjectionNo].ToString();
                                t.ObjectionDate = dr[recObjection.Fields.ObjectionDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recObjection.Fields.ObjectionDate].ToString());
                                t.SanctionID = dr[recObjection.Fields.SanctionID] == DBNull.Value ? (int?)null : int.Parse(dr[recObjection.Fields.SanctionID].ToString());
                                t.SanctionNo = dr[recObjection.Fields.SanctionNo] == DBNull.Value ? string.Empty : dr[recObjection.Fields.SanctionNo].ToString();
                                t.SanctionDate = dr[recObjection.Fields.SanctionDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recObjection.Fields.SanctionDate].ToString());
                                t.SanctionValue = dr[recObjection.Fields.SanctionValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recObjection.Fields.SanctionValue].ToString());
                                t.DebtorSectorID = dr[recObjection.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recObjection.Fields.DebtorSectorID].ToString());
                                t.ObjectionValue = dr[recObjection.Fields.ObjectionValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recObjection.Fields.ObjectionValue].ToString());
                                t.ObjectionNote = dr[recObjection.Fields.ObjectionNote] == DBNull.Value ? string.Empty : dr[recObjection.Fields.ObjectionNote].ToString();
                                t.TransNo = dr[recObjection.Fields.TransNo] == DBNull.Value ? string.Empty : dr[recObjection.Fields.TransNo].ToString();
                                t.TransStatus = dr[recObjection.Fields.TransStatus] == DBNull.Value ? string.Empty : dr[recObjection.Fields.TransStatus].ToString();
                                t.ResponNo = dr[recObjection.Fields.ResponNo] == DBNull.Value ? string.Empty : dr[recObjection.Fields.ResponNo].ToString();
                                t.ResponDate = dr[recObjection.Fields.ResponDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recObjection.Fields.ResponDate].ToString());
                                t.ResponNote = dr[recObjection.Fields.ResponNote] == DBNull.Value ? string.Empty : dr[recObjection.Fields.ResponNote].ToString();
                                t.Decision = dr[recObjection.Fields.Decision] == DBNull.Value ? string.Empty : dr[recObjection.Fields.Decision].ToString();
                                t.DecisionNo = dr[recObjection.Fields.DecisionNo] == DBNull.Value ? string.Empty : dr[recObjection.Fields.DecisionNo].ToString();
                                t.DecisionDate = dr[recObjection.Fields.DecisionDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recObjection.Fields.DecisionDate].ToString());
                                t.DecisionNote = dr[recObjection.Fields.DecisionNote] == DBNull.Value ? string.Empty : dr[recObjection.Fields.DecisionNote].ToString();
                                t.DecisionValue = dr[recObjection.Fields.DecisionValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recObjection.Fields.DecisionValue].ToString());
                                t.IsActive = dr[recObjection.Fields.IsActive] == DBNull.Value ? (bool?)null : bool.Parse(dr[recObjection.Fields.IsActive].ToString());
                                t.FileNameObjection = dr[recObjection.Fields.FileNameObjection] == DBNull.Value ? string.Empty : dr[recObjection.Fields.FileNameObjection].ToString();
                                t.FileNameRespon = dr[recObjection.Fields.FileNameRespon] == DBNull.Value ? string.Empty : dr[recObjection.Fields.FileNameRespon].ToString();
                                t.FileNameDecisionFile = dr[recObjection.Fields.FileNameDecisionFile] == DBNull.Value ? string.Empty : dr[recObjection.Fields.FileNameDecisionFile].ToString();
                                t.CreatedDate = dr[recObjection.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recObjection.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recObjection.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recObjection.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recObjection.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recObjection.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recObjection.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recObjection.Fields.UpdatedBy].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public List<recObjection> GetList(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<recObjection> tt = new List<recObjection>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);
                    
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat("      master.recDebtor.DebtorName, ObjectionID, ObjectionNo, ObjectionDate, SanctionID, SanctionNo, SanctionDate, SanctionValue, transactions.recObjection.DebtorSectorID, ObjectionValue, ObjectionNote, TransNo, TransStatus, ResponNo, ResponDate, ResponNote, Decision, DecisionNo, DecisionDate, DecisionNote, DecisionValue, transactions.recObjection.IsActive, FileNameObjection, FileNameRespon, FileNameDecisionFile, transactions.recObjection.CreatedDate, transactions.recObjection.CreatedBy, transactions.recObjection.UpdatedDate, transactions.recObjection.UpdatedBy  ");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat("      transactions.recObjection left join master.recDebtorSector on (transactions.recObjection.DebtorSectorID = master.recDebtorSector.DebtorSectorID) ");
                    sbQuery.AppendFormat("      left join master.recDebtor on (master.recDebtor.DebtorID = master.recDebtorSector.DebtorID) ");
                    //sbQuery.AppendFormat("    where transactions.recObjection.ResponNo IS Null  and transactions.recObjection.DecisionNo IS Null ");
                    sbQuery.AppendFormat("    {0} ", parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recObjection t = new recObjection(true);
                                t.ObjectionID = dr[recObjection.Fields.ObjectionID] == DBNull.Value ? 0 : int.Parse(dr[recObjection.Fields.ObjectionID].ToString());
                                t.recDebtor.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();
                                t.ObjectionNo = dr[recObjection.Fields.ObjectionNo] == DBNull.Value ? string.Empty : dr[recObjection.Fields.ObjectionNo].ToString();
                                t.ObjectionDate = dr[recObjection.Fields.ObjectionDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recObjection.Fields.ObjectionDate].ToString());
                                t.SanctionID = dr[recObjection.Fields.SanctionID] == DBNull.Value ? (int?)null : int.Parse(dr[recObjection.Fields.SanctionID].ToString());
                                t.SanctionNo = dr[recObjection.Fields.SanctionNo] == DBNull.Value ? string.Empty : dr[recObjection.Fields.SanctionNo].ToString();
                                t.SanctionDate = dr[recObjection.Fields.SanctionDate] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr[recObjection.Fields.SanctionDate].ToString());
                                t.SanctionValue = dr[recObjection.Fields.SanctionValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recObjection.Fields.SanctionValue].ToString());
                                t.DebtorSectorID = dr[recObjection.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recObjection.Fields.DebtorSectorID].ToString());
                                t.ObjectionValue = dr[recObjection.Fields.ObjectionValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recObjection.Fields.ObjectionValue].ToString());
                                t.ObjectionNote = dr[recObjection.Fields.ObjectionNote] == DBNull.Value ? string.Empty : dr[recObjection.Fields.ObjectionNote].ToString();
                                t.TransNo = dr[recObjection.Fields.TransNo] == DBNull.Value ? string.Empty : dr[recObjection.Fields.TransNo].ToString();
                                t.TransStatus = dr[recObjection.Fields.TransStatus] == DBNull.Value ? string.Empty : dr[recObjection.Fields.TransStatus].ToString();
                                t.ResponNo = dr[recObjection.Fields.ResponNo] == DBNull.Value ? string.Empty : dr[recObjection.Fields.ResponNo].ToString();
                                t.ResponDate = dr[recObjection.Fields.ResponDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recObjection.Fields.ResponDate].ToString());
                                t.ResponNote = dr[recObjection.Fields.ResponNote] == DBNull.Value ? string.Empty : dr[recObjection.Fields.ResponNote].ToString();
                                t.Decision = dr[recObjection.Fields.Decision] == DBNull.Value ? string.Empty : dr[recObjection.Fields.Decision].ToString();
                                t.DecisionNo = dr[recObjection.Fields.DecisionNo] == DBNull.Value ? string.Empty : dr[recObjection.Fields.DecisionNo].ToString();
                                t.DecisionDate = dr[recObjection.Fields.DecisionDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recObjection.Fields.DecisionDate].ToString());
                                t.DecisionNote = dr[recObjection.Fields.DecisionNote] == DBNull.Value ? string.Empty : dr[recObjection.Fields.DecisionNote].ToString();
                                t.DecisionValue = dr[recObjection.Fields.DecisionValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recObjection.Fields.DecisionValue].ToString());
                                t.IsActive = dr[recObjection.Fields.IsActive] == DBNull.Value ? (bool?)null : bool.Parse(dr[recObjection.Fields.IsActive].ToString());
                                t.FileNameObjection = dr[recObjection.Fields.FileNameObjection] == DBNull.Value ? string.Empty : dr[recObjection.Fields.FileNameObjection].ToString();
                                t.FileNameRespon = dr[recObjection.Fields.FileNameRespon] == DBNull.Value ? string.Empty : dr[recObjection.Fields.FileNameRespon].ToString();
                                t.FileNameDecisionFile = dr[recObjection.Fields.FileNameDecisionFile] == DBNull.Value ? string.Empty : dr[recObjection.Fields.FileNameDecisionFile].ToString();
                                t.CreatedDate = dr[recObjection.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recObjection.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recObjection.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recObjection.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recObjection.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recObjection.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recObjection.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recObjection.Fields.UpdatedBy].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public bool Save(recObjection objection, recBilling billing)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "[dbo].[SP_GenTransCode]";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@kode", SqlDbType.VarChar).Value = "12";
                    SqlParameter prmResult = new SqlParameter();
                    prmResult.ParameterName = "@hasil";
                    prmResult.Direction = ParameterDirection.Output;
                    prmResult.SqlDbType = SqlDbType.VarChar;
                    prmResult.Size = 100;
                    cmd.Parameters.Add(prmResult);
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    var transNo = prmResult.Value;
                    objection.TransNo = (string)transNo;

                    cmd = conn.CreateCommand();
                    cmd.CommandText = "INSERT INTO transactions.recObjection(ObjectionNo, ObjectionDate, SanctionID, SanctionNo, SanctionDate, SanctionValue, DebtorSectorID, ObjectionValue, ObjectionNote, TransNo, TransStatus, ResponNo, ResponDate, ResponNote, Decision, DecisionNo, DecisionDate, DecisionNote, DecisionValue, ObjectionFile, ResponFile, DecisionFile, BillingNo, IsActive, FileNameObjection, FileNameRespon, FileNameDecisionFile, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@ObjectionNo, @ObjectionDate, @SanctionID, @SanctionNo, @SanctionDate, @SanctionValue, @DebtorSectorID, @ObjectionValue, @ObjectionNote, @TransNo, @TransStatus, @ResponNo, @ResponDate, @ResponNote, @Decision, @DecisionNo, @DecisionDate, @DecisionNote, @DecisionValue, @ObjectionFile, @ResponFile, @DecisionFile, @BillingNo, @IsActive, @FileNameObjection, @FileNameRespon, @FileNameDecisionFile, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@ObjectionNo", SqlDbType.VarChar).Value = objection.ObjectionNo;
                    cmd.Parameters.Add("@ObjectionDate", SqlDbType.Date).Value = objection.ObjectionDate;
                    cmd.Parameters.Add("@SanctionID", SqlDbType.Int).Value = (object)objection.SanctionID ?? DBNull.Value;
                    cmd.Parameters.Add("@SanctionNo", SqlDbType.VarChar).Value = objection.SanctionNo;
                    cmd.Parameters.Add("@SanctionDate", SqlDbType.Date).Value = objection.SanctionDate;
                    cmd.Parameters.Add("@SanctionValue", SqlDbType.Decimal).Value = (object)objection.SanctionValue ?? DBNull.Value;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = objection.DebtorSectorID;
                    cmd.Parameters.Add("@ObjectionValue", SqlDbType.Decimal).Value = (object)objection.ObjectionValue ?? DBNull.Value;
                    cmd.Parameters.Add("@ObjectionNote", SqlDbType.VarChar).Value = (object)objection.ObjectionNote ?? DBNull.Value;
                    cmd.Parameters.Add("@TransNo", SqlDbType.VarChar).Value = (object)objection.TransNo ?? DBNull.Value;
                    cmd.Parameters.Add("@TransStatus", SqlDbType.VarChar).Value = (object)objection.TransStatus ?? DBNull.Value;
                    cmd.Parameters.Add("@ResponNo", SqlDbType.VarChar).Value = (object)objection.ResponNo ?? DBNull.Value;
                    cmd.Parameters.Add("@ResponDate", SqlDbType.Date).Value = (object)objection.ResponDate ?? DBNull.Value;
                    cmd.Parameters.Add("@ResponNote", SqlDbType.VarChar).Value = (object)objection.ResponNote ?? DBNull.Value;
                    cmd.Parameters.Add("@Decision", SqlDbType.VarChar).Value = (object)objection.Decision ?? DBNull.Value;
                    cmd.Parameters.Add("@DecisionNo", SqlDbType.VarChar).Value = (object)objection.DecisionNo ?? DBNull.Value;
                    cmd.Parameters.Add("@DecisionDate", SqlDbType.Date).Value = (object)objection.DecisionDate ?? DBNull.Value;
                    cmd.Parameters.Add("@DecisionNote", SqlDbType.VarChar).Value = (object)objection.DecisionNote ?? DBNull.Value;
                    cmd.Parameters.Add("@DecisionValue", SqlDbType.Decimal).Value = (object)objection.DecisionValue ?? DBNull.Value;
                    cmd.Parameters.Add("@ObjectionFile", SqlDbType.VarBinary).Value = (object)objection.ObjectionFile ?? DBNull.Value;
                    cmd.Parameters.Add("@ResponFile", SqlDbType.VarBinary).Value = (object)objection.ResponFile ?? DBNull.Value;
                    cmd.Parameters.Add("@DecisionFile", SqlDbType.VarBinary).Value = (object)objection.DecisionFile ?? DBNull.Value;
                    cmd.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)objection.BillingNo ?? DBNull.Value;
                    cmd.Parameters.Add("@IsActive", SqlDbType.Bit).Value = (object)objection.IsActive ?? DBNull.Value;
                    cmd.Parameters.Add("@FileNameObjection", SqlDbType.VarChar).Value = (object)objection.FileNameObjection ?? DBNull.Value;
                    cmd.Parameters.Add("@FileNameRespon", SqlDbType.VarChar).Value = (object)objection.FileNameRespon ?? DBNull.Value;
                    cmd.Parameters.Add("@FileNameDecisionFile", SqlDbType.VarChar).Value = (object)objection.FileNameDecisionFile ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)objection.CreatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)objection.CreatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)objection.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)objection.UpdatedBy ?? DBNull.Value;
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery(); // Save recObjection
                    cmd.Dispose();
                    //txn.Commit();

                    cmd = conn.CreateCommand();
                    cmd.CommandText = "UPDATE transactions.recBilling SET IsHold = 1, ObjectionNo = @ObjectionNo, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE BillingNo = @BillingNo";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = billing.BillingNo;
                    cmd.Parameters.Add("@ObjectionNo", SqlDbType.VarChar).Value = billing.ObjectionNo;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)billing.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)billing.UpdatedBy ?? DBNull.Value;
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery(); // Update recBilling => IsHold =1
                    cmd.Dispose();

                    txn.Commit();
                    result = true;
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }

            }
            return result;
        }

        public bool UpdateWithoutFile(recObjection domain)
        {
            int result = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "UPDATE transactions.recObjection SET ObjectionNo = @ObjectionNo, ObjectionDate = @ObjectionDate, SanctionID = @SanctionID, SanctionNo = @SanctionNo, SanctionDate = @SanctionDate, SanctionValue = @SanctionValue, DebtorSectorID = @DebtorSectorID, ObjectionNote = @ObjectionNote, TransNo = @TransNo, TransStatus = @TransStatus, IsActive = @IsActive, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE ObjectionID = @ObjectionID";
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.Add("@ObjectionID", SqlDbType.Int).Value = domain.ObjectionID;
                    cmd.Parameters.Add("@ObjectionNo", SqlDbType.VarChar).Value = domain.ObjectionNo;
                    cmd.Parameters.Add("@ObjectionDate", SqlDbType.Date).Value = domain.ObjectionDate;
                    cmd.Parameters.Add("@SanctionID", SqlDbType.Int).Value = (object)domain.SanctionID ?? DBNull.Value;
                    cmd.Parameters.Add("@SanctionNo", SqlDbType.VarChar).Value = domain.SanctionNo;
                    cmd.Parameters.Add("@SanctionDate", SqlDbType.Date).Value = domain.SanctionDate;
                    cmd.Parameters.Add("@SanctionValue", SqlDbType.Decimal).Value = (object)domain.SanctionValue ?? DBNull.Value;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = domain.DebtorSectorID;
                    //cmd.Parameters.Add("@ObjectionValue", SqlDbType.Decimal).Value = (object)domain.ObjectionValue ?? DBNull.Value;
                    cmd.Parameters.Add("@ObjectionNote", SqlDbType.VarChar).Value = (object)domain.ObjectionNote ?? DBNull.Value;

                    cmd.Parameters.Add("@TransNo", SqlDbType.VarChar).Value = (object)domain.TransNo ?? DBNull.Value;
                    cmd.Parameters.Add("@TransStatus", SqlDbType.VarChar).Value = (object)domain.TransStatus ?? DBNull.Value;

                    cmd.Parameters.Add("@IsActive", SqlDbType.Bit).Value = (object)domain.IsActive ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;

                    try
                    {
                        conn.Open();
                        result = cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return (result > 0);
        }

        public bool UpdateResponse(recObjection domain)
        {
            int result = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "UPDATE transactions.recObjection SET ResponNo = @ResponNo, ResponDate = @ResponDate, ResponNote = @ResponNote, ResponFile = @ResponFile, IsActive = @IsActive, FileNameRespon = @FileNameRespon, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE ObjectionID = @ObjectionID";
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.Add("@ObjectionID", SqlDbType.Int).Value = domain.ObjectionID;
                    cmd.Parameters.Add("@ResponNo", SqlDbType.VarChar).Value = (object)domain.ResponNo ?? DBNull.Value;
                    cmd.Parameters.Add("@ResponDate", SqlDbType.Date).Value = (object)domain.ResponDate ?? DBNull.Value;
                    cmd.Parameters.Add("@ResponNote", SqlDbType.VarChar).Value = (object)domain.ResponNote ?? DBNull.Value;
                    cmd.Parameters.Add("@ResponFile", SqlDbType.VarBinary).Value = (object)domain.ResponFile ?? DBNull.Value;
                    cmd.Parameters.Add("@IsActive", SqlDbType.Bit).Value = (object)domain.IsActive ?? DBNull.Value;
                    cmd.Parameters.Add("@FileNameRespon", SqlDbType.VarChar).Value = (object)domain.FileNameRespon ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;

                    try
                    {
                        conn.Open();
                        result = cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return (result > 0);
        }

        public bool UpdateResponseWithoutFile(recObjection domain)
        {
            int result = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "UPDATE transactions.recObjection SET ResponNo = @ResponNo, ResponDate = @ResponDate, ResponNote = @ResponNote, IsActive = @IsActive, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE ObjectionID = @ObjectionID";
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.Add("@ObjectionID", SqlDbType.Int).Value = domain.ObjectionID;
                    cmd.Parameters.Add("@ResponNo", SqlDbType.VarChar).Value = (object)domain.ResponNo ?? DBNull.Value;
                    cmd.Parameters.Add("@ResponDate", SqlDbType.Date).Value = (object)domain.ResponDate ?? DBNull.Value;
                    cmd.Parameters.Add("@ResponNote", SqlDbType.VarChar).Value = (object)domain.ResponNote ?? DBNull.Value;
                    cmd.Parameters.Add("@IsActive", SqlDbType.Bit).Value = (object)domain.IsActive ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;

                    try
                    {
                        conn.Open();
                        result = cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return (result > 0);
        }

        public bool UpdateDecision(recObjection domain, recBilling billing)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    SqlCommand cmd = conn.CreateCommand();

                    cmd.CommandText = "[dbo].[SP_GenTransCode]";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@kode", SqlDbType.VarChar).Value = "12";
                    SqlParameter prmResult = new SqlParameter();
                    prmResult.ParameterName = "@hasil";
                    prmResult.Direction = ParameterDirection.Output;
                    prmResult.SqlDbType = SqlDbType.VarChar;
                    prmResult.Size = 100;
                    cmd.Parameters.Add(prmResult);
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    var transNo = prmResult.Value;
                    billing.BillingNo = (string)transNo;

                    SqlCommand cmdd = new SqlCommand("SELECT dbo.FN_GetTransType(@TransCode) ", conn);
                    cmdd.CommandType = CommandType.Text;
                    cmdd.Parameters.Add("@TransCode", SqlDbType.VarChar).Value = "12";
                    cmdd.Transaction = txn;
                    var transtype = Int16.Parse(cmdd.ExecuteScalar().ToString());
                    billing.TransTypeID = transtype;
                    cmdd.Dispose();

                    cmd = conn.CreateCommand();
                    cmd.CommandText = "UPDATE transactions.recObjection SET ResponNo = @ResponNo, ResponDate = @ResponDate, TransStatus = @TransStatus, Decision = @Decision, DecisionNo = @DecisionNo, DecisionDate = @DecisionDate, DecisionNote = @DecisionNote, DecisionValue = @DecisionValue, DecisionFile = @DecisionFile, FileNameDecisionFile = @FileNameDecisionFile,  IsActive = @IsActive, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE ObjectionID = @ObjectionID";
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.Add("@ObjectionID", SqlDbType.Int).Value = domain.ObjectionID;
                    cmd.Parameters.Add("@ResponNo", SqlDbType.VarChar).Value = (object)domain.ResponNo ?? DBNull.Value;
                    cmd.Parameters.Add("@ResponDate", SqlDbType.Date).Value = (object)domain.ResponDate ?? DBNull.Value;
                    cmd.Parameters.Add("@TransStatus", SqlDbType.VarChar).Value = (object)domain.TransStatus ?? DBNull.Value;
                    cmd.Parameters.Add("@Decision", SqlDbType.VarChar).Value = (object)domain.Decision ?? DBNull.Value;
                    cmd.Parameters.Add("@DecisionNo", SqlDbType.VarChar).Value = (object)domain.DecisionNo ?? DBNull.Value;
                    cmd.Parameters.Add("@DecisionDate", SqlDbType.Date).Value = (object)domain.DecisionDate ?? DBNull.Value;
                    cmd.Parameters.Add("@DecisionNote", SqlDbType.VarChar).Value = (object)domain.DecisionNote ?? DBNull.Value;
                    cmd.Parameters.Add("@DecisionValue", SqlDbType.Decimal).Value = (object)domain.DecisionValue ?? DBNull.Value;

                    cmd.Parameters.Add("@DecisionFile", SqlDbType.VarBinary).Value = (object)domain.DecisionFile ?? DBNull.Value;
                    cmd.Parameters.Add("@FileNameDecisionFile", SqlDbType.VarChar).Value = (object)domain.FileNameDecisionFile ?? DBNull.Value;

                    cmd.Parameters.Add("@IsActive", SqlDbType.Bit).Value = (object)domain.IsActive ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery(); 
                    cmd.Dispose();
                    //txn.Commit();

                    //cmd = conn.CreateCommand();
                    //cmd.CommandText = "UPDATE transactions.recBilling SET IsObjectionStatus = 1, UpdatedDate = @UpdatedDate1, UpdatedBy = @UpdatedBy1 WHERE ObjectionNo = @ObjectionNo";
                    //cmd.CommandType = CommandType.Text;
                    //cmd.Parameters.Add("@ObjectionNo", SqlDbType.VarChar).Value = billing.ObjectionNo;
                    //cmd.Parameters.Add("@UpdatedDate1", SqlDbType.DateTime).Value = (object)billing.UpdatedDate ?? DBNull.Value;
                    //cmd.Parameters.Add("@UpdatedBy1", SqlDbType.VarChar).Value = (object)billing.UpdatedBy ?? DBNull.Value;
                    //cmd.Transaction = txn;
                    //cmd.ExecuteNonQuery();
                    //cmd.Dispose();

                    cmd = conn.CreateCommand();
                    cmd.CommandText = "INSERT INTO transactions.recBilling(BillingNo, DebtorSectorID, TranstypeID, TariffID, Value, InvoiceNo, InvoiceDetailID, Balance, PrevBillingNo, BillingDate, DueDate, IsCancel, ForPayment, SaikoNo, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, CalculationID, PaymentValue,IsManual, NoHoldBilling, IsHold, IsObjectionStatus, IsRestructur, IsPosting, ObjectionNo ) VALUES(@BillingNo, @DebtorSectorID, @TranstypeID, @TariffID, @Value, @InvoiceNo, @InvoiceDetailID, @Balance, @PrevBillingNo, @BillingDate, @DueDate, @IsCancel, @ForPayment, @SaikoNo, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy, @CalculationID, @PaymentValue, @IsManual, @NoHoldBilling, @IsHold, @IsObjectionStatus, @IsRestructur, @IsPosting, @ObjectionNo )";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = billing.BillingNo;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = billing.DebtorSectorID;
                    cmd.Parameters.Add("@TranstypeID", SqlDbType.SmallInt).Value = (object)billing.TransTypeID ?? DBNull.Value;
                    cmd.Parameters.Add("@TariffID", SqlDbType.Int).Value = (object)billing.TariffID ?? DBNull.Value;
                    cmd.Parameters.Add("@Value", SqlDbType.Decimal).Value = billing.Value;
                    cmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)billing.InvoiceNo ?? DBNull.Value;
                    cmd.Parameters.Add("@InvoiceDetailID", SqlDbType.SmallInt).Value = (object)billing.InvoiceDetailID ?? DBNull.Value;
                    cmd.Parameters.Add("@Balance", SqlDbType.Decimal).Value = billing.Balance;
                    cmd.Parameters.Add("@PrevBillingNo", SqlDbType.VarChar).Value = (object)billing.PrevBillingNo ?? DBNull.Value;
                    cmd.Parameters.Add("@BillingDate", SqlDbType.DateTime).Value = (object)billing.PrevBillingNo ?? DBNull.Value;
                    cmd.Parameters.Add("@DueDate", SqlDbType.Date).Value = (object)billing.DueDate ?? DBNull.Value; 
                    cmd.Parameters.Add("@IsCancel", SqlDbType.Bit).Value = (object)billing.IsCancel ?? DBNull.Value;
                    cmd.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = (object)billing.ForPayment ?? DBNull.Value;
                    cmd.Parameters.Add("@SaikoNo", SqlDbType.VarChar).Value = (object)billing.SaikoNo ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)billing.CreatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)billing.CreatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)billing.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.Date).Value = (object)billing.UpdatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@CalculationID", SqlDbType.Int).Value = (object)billing.CalculationID ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentValue", SqlDbType.Decimal).Value = (object)billing.PaymentValue ?? DBNull.Value;
                    cmd.Parameters.Add("@IsManual", SqlDbType.Bit).Value = (object)billing.IsManual ?? DBNull.Value;
                    cmd.Parameters.Add("@NoHoldBilling", SqlDbType.VarChar).Value = (object)billing.NoHoldBilling ?? DBNull.Value;
                    cmd.Parameters.Add("@IsHold", SqlDbType.Bit).Value = (object)billing.IsHold ?? DBNull.Value;
                    cmd.Parameters.Add("@IsObjectionStatus", SqlDbType.Bit).Value = (object)billing.IsObjectionStatus ?? DBNull.Value;
                    cmd.Parameters.Add("@IsRestructur", SqlDbType.Bit).Value = (object)billing.IsRestructur ?? DBNull.Value;
                    cmd.Parameters.Add("@IsPosting", SqlDbType.Bit).Value = (object)billing.IsPosting ?? DBNull.Value;
                    cmd.Parameters.Add("@ObjectionNo", SqlDbType.VarChar).Value = (object)billing.ObjectionNo ?? DBNull.Value;
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery(); // Insert recBilling
                    cmd.Dispose();

                    //cmd = conn.CreateCommand();
                    //cmd.CommandText = "Update transactions.recBilling SET Value = @Value, Balance = @Balance where ObjectionNo = @ObjectionNo";
                    //cmd.CommandType = CommandType.Text;
                    //cmd.Parameters.Add("@ObjectionNo", SqlDbType.VarChar).Value = (object)billing.ObjectionNo ?? DBNull.Value;
                    //cmd.Parameters.Add("@Value", SqlDbType.Decimal).Value = billing.Value;
                    //cmd.Parameters.Add("@Balance", SqlDbType.Decimal).Value = billing.Balance;
                    //cmd.Transaction = txn;
                    //cmd.ExecuteNonQuery();
                    //cmd.Dispose();


                    txn.Commit();
                    result = true;
                }
                    catch (Exception ex)
                    {
                        txn.Rollback();
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            return result;
        }

        public bool UpdateDecisionWithoutFile(recObjection domain, recBilling billing)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "[dbo].[SP_GenTransCode]";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@kode", SqlDbType.VarChar).Value = "12";
                    SqlParameter prmResult = new SqlParameter();
                    prmResult.ParameterName = "@hasil";
                    prmResult.Direction = ParameterDirection.Output;
                    prmResult.SqlDbType = SqlDbType.VarChar;
                    prmResult.Size = 100;
                    cmd.Parameters.Add(prmResult);
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    var transNo = prmResult.Value;
                    billing.BillingNo = (string)transNo;

                    SqlCommand cmdd = new SqlCommand("SELECT dbo.FN_GetTransType(@TransCode) ", conn);
                    cmdd.CommandType = CommandType.Text;
                    cmdd.Parameters.Add("@TransCode", SqlDbType.VarChar).Value = "12";
                    cmdd.Transaction = txn;
                    var transtype = Int16.Parse(cmdd.ExecuteScalar().ToString());
                    billing.TransTypeID = transtype;
                    cmdd.Dispose();

                    cmd = conn.CreateCommand();
                    cmd.CommandText = "UPDATE transactions.recObjection SET ResponNo = @ResponNo, ResponDate = @ResponDate, TransStatus = @TransStatus, Decision = @Decision, DecisionNo = @DecisionNo, DecisionDate = @DecisionDate, DecisionNote = @DecisionNote, DecisionValue = @DecisionValue, IsActive = @IsActive, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE ObjectionID = @ObjectionID";
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.Add("@ObjectionID", SqlDbType.Int).Value = domain.ObjectionID;
                    cmd.Parameters.Add("@ResponNo", SqlDbType.VarChar).Value = (object)domain.ResponNo ?? DBNull.Value;
                    cmd.Parameters.Add("@ResponDate", SqlDbType.Date).Value = (object)domain.ResponDate ?? DBNull.Value;
                    cmd.Parameters.Add("@TransStatus", SqlDbType.VarChar).Value = (object)domain.TransStatus ?? DBNull.Value;
                    cmd.Parameters.Add("@Decision", SqlDbType.VarChar).Value = (object)domain.Decision ?? DBNull.Value;
                    cmd.Parameters.Add("@DecisionNo", SqlDbType.VarChar).Value = (object)domain.DecisionNo ?? DBNull.Value;
                    cmd.Parameters.Add("@DecisionDate", SqlDbType.Date).Value = (object)domain.DecisionDate ?? DBNull.Value;
                    cmd.Parameters.Add("@DecisionNote", SqlDbType.VarChar).Value = (object)domain.DecisionNote ?? DBNull.Value;
                    cmd.Parameters.Add("@DecisionValue", SqlDbType.Decimal).Value = (object)domain.DecisionValue ?? DBNull.Value;

                    cmd.Parameters.Add("@IsActive", SqlDbType.Bit).Value = (object)domain.IsActive ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    //txn.Commit();

                    //cmd.CommandText = "UPDATE transactions.recBilling SET IsObjectionStatus = 1, UpdatedDate = @UpdatedDate1, UpdatedBy = @UpdatedBy1 WHERE ObjectionNo = @ObjectionNo";
                    //cmd.CommandType = CommandType.Text;
                    //cmd.Parameters.Add("@ObjectionNo", SqlDbType.VarChar).Value = billing.ObjectionNo;
                    //cmd.Parameters.Add("@UpdatedDate1", SqlDbType.DateTime).Value = (object)billing.UpdatedDate ?? DBNull.Value;
                    //cmd.Parameters.Add("@UpdatedBy1", SqlDbType.VarChar).Value = (object)billing.UpdatedBy ?? DBNull.Value;
                    //cmd.Transaction = txn;
                    //cmd.ExecuteNonQuery();
                    //cmd.Dispose();

                    cmd = conn.CreateCommand();
                    cmd.CommandText = "INSERT INTO transactions.recBilling(BillingNo, DebtorSectorID, TranstypeID, TariffID, Value, InvoiceNo, InvoiceDetailID, Balance, PrevBillingNo, BillingDate, DueDate, IsCancel, ForPayment, SaikoNo, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, CalculationID, PaymentValue,IsManual, NoHoldBilling, IsHold, IsObjectionStatus, IsRestructur, IsPosting, ObjectionNo ) VALUES(@BillingNo, @DebtorSectorID, @TranstypeID, @TariffID, @Value, @InvoiceNo, @InvoiceDetailID, @Balance, @PrevBillingNo, @BillingDate, @DueDate, @IsCancel, @ForPayment, @SaikoNo, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy, @CalculationID, @PaymentValue, @IsManual, @NoHoldBilling, @IsHold, @IsObjectionStatus, @IsRestructur, @IsPosting, @ObjectionNo )";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = billing.BillingNo;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = billing.DebtorSectorID;
                    cmd.Parameters.Add("@TranstypeID", SqlDbType.SmallInt).Value = (object)billing.TransTypeID ?? DBNull.Value;
                    cmd.Parameters.Add("@TariffID", SqlDbType.Int).Value = (object)billing.TariffID ?? DBNull.Value;
                    cmd.Parameters.Add("@Value", SqlDbType.Decimal).Value = billing.Value;
                    cmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)billing.InvoiceNo ?? DBNull.Value;
                    cmd.Parameters.Add("@InvoiceDetailID", SqlDbType.SmallInt).Value = (object)billing.InvoiceDetailID ?? DBNull.Value;
                    cmd.Parameters.Add("@Balance", SqlDbType.Decimal).Value = billing.Balance;
                    cmd.Parameters.Add("@PrevBillingNo", SqlDbType.VarChar).Value = (object)billing.PrevBillingNo ?? DBNull.Value;
                    cmd.Parameters.Add("@BillingDate", SqlDbType.DateTime).Value = (object)billing.BillingDate ?? DBNull.Value;
                    cmd.Parameters.Add("@DueDate", SqlDbType.Date).Value = (object)billing.DueDate ?? DBNull.Value; 
                    cmd.Parameters.Add("@IsCancel", SqlDbType.Bit).Value = (object)billing.IsCancel ?? DBNull.Value;
                    cmd.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = (object)billing.ForPayment ?? DBNull.Value;
                    cmd.Parameters.Add("@SaikoNo", SqlDbType.VarChar).Value = (object)billing.SaikoNo ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)billing.CreatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)billing.CreatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)billing.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.Date).Value = (object)billing.UpdatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@CalculationID", SqlDbType.Int).Value = (object)billing.CalculationID ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentValue", SqlDbType.Decimal).Value = (object)billing.PaymentValue ?? DBNull.Value;
                    cmd.Parameters.Add("@IsManual", SqlDbType.Bit).Value = (object)billing.IsManual ?? DBNull.Value;
                    cmd.Parameters.Add("@NoHoldBilling", SqlDbType.VarChar).Value = (object)billing.NoHoldBilling ?? DBNull.Value;
                    cmd.Parameters.Add("@IsHold", SqlDbType.Bit).Value = (object)billing.IsHold ?? DBNull.Value;
                    cmd.Parameters.Add("@IsObjectionStatus", SqlDbType.Bit).Value = (object)billing.IsObjectionStatus ?? DBNull.Value;
                    cmd.Parameters.Add("@IsRestructur", SqlDbType.Bit).Value = (object)billing.IsRestructur ?? DBNull.Value;
                    cmd.Parameters.Add("@IsPosting", SqlDbType.Bit).Value = (object)billing.IsPosting ?? DBNull.Value;
                    cmd.Parameters.Add("@ObjectionNo", SqlDbType.VarChar).Value = (object)billing.ObjectionNo ?? DBNull.Value;
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery(); // Insert recBilling
                    cmd.Dispose();

                    txn.Commit();
                    result = true;
                    }
                    catch (Exception ex)
                    {
                        txn.Rollback();
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            return result;
        }

        public bool EditUpdateDecisionWithoutFile(recObjection domain, recBilling billing)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    SqlCommand cmd = conn.CreateCommand();

                    cmd.CommandText = "UPDATE transactions.recObjection SET ResponNo = @ResponNo, ResponDate = @ResponDate, TransStatus = @TransStatus, Decision = @Decision, DecisionNo = @DecisionNo, DecisionDate = @DecisionDate, DecisionNote = @DecisionNote, DecisionValue = @DecisionValue, IsActive = @IsActive, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE ObjectionID = @ObjectionID";
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.Add("@ObjectionID", SqlDbType.Int).Value = domain.ObjectionID;
                    cmd.Parameters.Add("@ResponNo", SqlDbType.VarChar).Value = (object)domain.ResponNo ?? DBNull.Value;
                    cmd.Parameters.Add("@ResponDate", SqlDbType.Date).Value = (object)domain.ResponDate ?? DBNull.Value;
                    cmd.Parameters.Add("@TransStatus", SqlDbType.VarChar).Value = (object)domain.TransStatus ?? DBNull.Value;
                    cmd.Parameters.Add("@Decision", SqlDbType.VarChar).Value = (object)domain.Decision ?? DBNull.Value;
                    cmd.Parameters.Add("@DecisionNo", SqlDbType.VarChar).Value = (object)domain.DecisionNo ?? DBNull.Value;
                    cmd.Parameters.Add("@DecisionDate", SqlDbType.Date).Value = (object)domain.DecisionDate ?? DBNull.Value;
                    cmd.Parameters.Add("@DecisionNote", SqlDbType.VarChar).Value = (object)domain.DecisionNote ?? DBNull.Value;
                    cmd.Parameters.Add("@DecisionValue", SqlDbType.Decimal).Value = (object)domain.DecisionValue ?? DBNull.Value;
                    cmd.Parameters.Add("@IsActive", SqlDbType.Bit).Value = (object)domain.IsActive ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    cmd = conn.CreateCommand();
                    cmd.CommandText = "Update transactions.recBilling SET Value = @Value, Balance = @Balance where ObjectionNo = @ObjectionNo and IsObjectionStatus IS NULL and IsHold IS NULL ";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@ObjectionNo", SqlDbType.VarChar).Value = (object)billing.ObjectionNo ?? DBNull.Value;
                    cmd.Parameters.Add("@Value", SqlDbType.Decimal).Value = billing.Value;
                    cmd.Parameters.Add("@Balance", SqlDbType.Decimal).Value = billing.Balance;
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    txn.Commit();
                    result = true;
                    }
                    catch (Exception ex)
                    {
                        txn.Rollback();
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            return result;
        }

        public bool EditUpdateDecision(recObjection domain, recBilling billing)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    SqlCommand cmd = conn.CreateCommand();


                    cmd = conn.CreateCommand();
                    cmd.CommandText = "UPDATE transactions.recObjection SET ResponNo = @ResponNo, ResponDate = @ResponDate, TransStatus = @TransStatus, Decision = @Decision, DecisionNo = @DecisionNo, DecisionDate = @DecisionDate, DecisionNote = @DecisionNote, DecisionValue = @DecisionValue, DecisionFile = @DecisionFile, FileNameDecisionFile = @FileNameDecisionFile,  IsActive = @IsActive, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE ObjectionID = @ObjectionID";
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.Add("@ObjectionID", SqlDbType.Int).Value = domain.ObjectionID;
                    cmd.Parameters.Add("@ResponNo", SqlDbType.VarChar).Value = (object)domain.ResponNo ?? DBNull.Value;
                    cmd.Parameters.Add("@ResponDate", SqlDbType.Date).Value = (object)domain.ResponDate ?? DBNull.Value;
                    cmd.Parameters.Add("@TransStatus", SqlDbType.VarChar).Value = (object)domain.TransStatus ?? DBNull.Value;
                    cmd.Parameters.Add("@Decision", SqlDbType.VarChar).Value = (object)domain.Decision ?? DBNull.Value;
                    cmd.Parameters.Add("@DecisionNo", SqlDbType.VarChar).Value = (object)domain.DecisionNo ?? DBNull.Value;
                    cmd.Parameters.Add("@DecisionDate", SqlDbType.Date).Value = (object)domain.DecisionDate ?? DBNull.Value;
                    cmd.Parameters.Add("@DecisionNote", SqlDbType.VarChar).Value = (object)domain.DecisionNote ?? DBNull.Value;
                    cmd.Parameters.Add("@DecisionValue", SqlDbType.Decimal).Value = (object)domain.DecisionValue ?? DBNull.Value;
                    cmd.Parameters.Add("@DecisionFile", SqlDbType.VarBinary).Value = (object)domain.DecisionFile ?? DBNull.Value;
                    cmd.Parameters.Add("@FileNameDecisionFile", SqlDbType.VarChar).Value = (object)domain.FileNameDecisionFile ?? DBNull.Value;
                    cmd.Parameters.Add("@IsActive", SqlDbType.Bit).Value = (object)domain.IsActive ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    cmd = conn.CreateCommand();
                    cmd.CommandText = "Update transactions.recBilling SET Value = @Value, Balance = @Balance where ObjectionNo = @ObjectionNo and IsHold IS NULL and IsObjectionStatus IS NULL";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@ObjectionNo", SqlDbType.VarChar).Value = (object)billing.ObjectionNo ?? DBNull.Value;
                    cmd.Parameters.Add("@Value", SqlDbType.Decimal).Value = billing.Value;
                    cmd.Parameters.Add("@Balance", SqlDbType.Decimal).Value = billing.Balance;
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    txn.Commit();
                    result = true;
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }

        public int ValidasiCode(string Code)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("select count(ObjectionID) from transactions.recObjection where lower(ObjectionNo)='" + Code.ToLower() + "'", conn);
            try
            {
                conn.Open();
                return ((int)cmd.ExecuteScalar());
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                if (conn.State == ConnectionState.Open) conn.Close();
            }
        }

        public bool DeleteBillingNo(recBilling billing)
        {
            int result = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "Delete from transactions.recBilling where BillingNo = @BillingNo";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = billing.BillingNo;

                    try
                    {
                        conn.Open();
                        result = cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                        {
                            throw (ex);
                        }
                        finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return (result > 0);
        }

    }
}
